package servlet.notice;

import com.alibaba.fastjson.JSONObject;
import po.Notice;
import utils.JdbcUtil;
import utils.ResultData;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@WebServlet(urlPatterns = "/noticeList")
public class NoticeList extends HttpServlet {
    JdbcUtil jdbcUtil=new JdbcUtil();

    protected void service(HttpServletRequest req, HttpServletResponse resp){
        //获取当前页和条数
        Integer page = Integer.valueOf(req.getParameter("page"));
        Integer limit = Integer.valueOf(req.getParameter("limit"));
        Integer curent=(page-1)*limit;
        //获取带条件搜索的值
        String name = req.getParameter("name");
        String content = req.getParameter("content");
        Connection connection=jdbcUtil.getConnection();
        String sql="select * from notice where 1=1";
        //PreparedStatement preparedStatement=null;
        try {
            //动态sql拼接
            StringBuffer stringBuffer=new StringBuffer();
            stringBuffer.append(sql);
            //记录当前占位符的顺序
            int i=0;
            if (name!=null&&name.trim().length()!=0){
                stringBuffer.append(" and name like ?");
            }
            if (content!=null&&content.trim().length()!=0){
                stringBuffer.append(" and content like ?");
            }
            PreparedStatement preparedStatement=null;

            preparedStatement = connection.prepareStatement(stringBuffer.toString());
            if (name!=null&&name.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+name+"%");
            }
            if (content!=null&&content.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+content+"%");
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            //光标最后一位
            resultSet.last();
            Integer count = resultSet.getRow();
            //分页的sql拼接
            stringBuffer.append(" limit ?,?");
            preparedStatement= connection.prepareStatement(stringBuffer.toString());
            //设置分页参数的占位符
            i=0;
            if (name!=null&&name.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+name+"%");
            }
            if (content!=null&&content.trim().length()!=0){
                ++i;
                preparedStatement.setString(i,"%"+content+"%");
            }
            ++i;
            preparedStatement.setInt(i,curent);
            ++i;
            preparedStatement.setInt(i,limit);
            resultSet=preparedStatement.executeQuery();
            List<Notice> noticeList=new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name1 = resultSet.getString("name");
                String content1=resultSet.getString("content");
                String user = resultSet.getString("user");
                String releaseTime = dateFormat( resultSet.getTimestamp("release_time"));
                Notice notice= new Notice(id,name1,content1,user,releaseTime);
                noticeList.add(notice);
            }
            ResultData page1 = ResultData.page(noticeList, count.longValue());
            Object o = JSONObject.toJSON(page1);
            resp.getWriter().print(o.toString());


        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
    //时间转换
    public  String dateFormat(Timestamp date) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
        String format = simpleDateFormat.format(date);
        return format;

    }

}
